package com.yang.jpa.dao;

import com.yang.jpa.model.CheckAttendanceSheet;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface CheckAttendanceSheetDao extends JpaRepository<CheckAttendanceSheet, Integer>, JpaSpecificationExecutor<CheckAttendanceSheet> {

//    select cas.* from check_attendance_sheet cas inner join attendance_sheet a on cas.attendance_sheet_id = a.id inner join staff s on a.staff_id = s.id inner join department d on d.id = s.department_id where d.organ_id like ? and length(d.organ_id) = ? and cas.attendance_sheet_state = ?
    @Query(value = "select cas.* from check_attendance_sheet cas inner join attendance_sheet a on cas.attendance_sheet_id = a.id inner join staff s on a.staff_id = s.id where s.organ_id like ?1 and length(s.organ_id) = ?3 and cas.attendance_sheet_state = ?2", nativeQuery = true)
    List<CheckAttendanceSheet> findCheckAttendanceSheet(String organId, int attendanceSheetState, int organLen);

    @Query(value = "select cas.* from check_attendance_sheet cas inner join attendance_sheet a on cas.attendance_sheet_id = a.id inner join staff s on a.staff_id = s.id where cas.attendance_sheet_state = ?", nativeQuery = true)
    List<CheckAttendanceSheet> findCheckAttendanceSheetNew(int attendanceSheetState);

}
